package com.fact.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.fact.dto.EntrySetDTO;
import com.fact.dto.NodeHierarchyDTO;
import com.fact.dto.ScenarioDTO;
import com.fact.utils.connection.ConnectionManager;

public class EntrySetService {
	
	
	public ArrayList<EntrySetDTO> getEntrySetList(int month,int year)
	{
		ArrayList<EntrySetDTO> entrySetList=new ArrayList<EntrySetDTO>();
		Connection conn = ConnectionManager.getConnection();
		   String sql = "SELECT   tmp2.* " +
			"    FROM (SELECT tmp1.*, entry_sets.entry_set_id,status " +
			"            FROM (SELECT mst_makes.make_id, mst_makes.make_name, " +
			"                         mst_markets.market_id, mst_markets.market_name, " +
			"                         mst_segments.* " +
			"                    FROM make_market_combination, " +
			"                         mst_makes, " +
			"                         mst_markets, " +
			"                         mst_segments " +
			"                   WHERE make_market_combination.make_id = mst_makes.make_id " +
			"                     AND make_market_combination.market_id = " +
			"                                                         mst_markets.market_id) tmp1 " +
			"                 LEFT OUTER JOIN " +
			"                 entry_sets " +
			"                 ON tmp1.make_id = entry_sets.make_id " +
			"               AND tmp1.market_id = entry_sets.market_id " +
			"               AND tmp1.segment_id = entry_sets.segment_id " +
			"               AND MONTH = ? " +
			"               AND YEAR = ? " +
			"                 ) tmp2 " +
			"         LEFT OUTER JOIN " +
			"         mst_segments ON tmp2.segment_id = mst_segments.segment_id " +
			"ORDER BY tmp2.make_id, market_id, tmp2.segment_id ";
		   
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1, month);
				stmt.setInt(2, year);				
				r = stmt.executeQuery();
				EntrySetDTO es=null;
				while (r.next())
				{
					es=new EntrySetDTO();
					
					es.setEntrySetId(r.getInt("ENTRY_SET_ID"));
					es.setMakeId(r.getInt("MAKE_ID"));
					es.setMakeName(r.getString("MAKE_NAME"));
					es.setMarketId(r.getInt("MARKET_ID"));
					es.setMarketName(r.getString("MARKET_NAME"));
					
					es.setSegmentId(r.getInt("SEGMENT_ID"));
					es.setSegmentName(r.getString("SEGMENT_NAME"));
					
					es.setStatus(r.getString("STATUS"));
					es.setMonth(month);
					es.setYear(year);
					entrySetList.add(es);
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
		
		
		return entrySetList;
	}
	
	public boolean createEntrySet(EntrySetDTO entrySet,ArrayList<NodeHierarchyDTO> nodeList,ArrayList<ScenarioDTO> scenarioList)
	{
		int entrySetId=0;
		
		Connection conn = ConnectionManager.getConnection();
		String sql = " Select SQN_ENTRYSETS.NEXTVAL from dual";
		   boolean response=false;
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				r = stmt.executeQuery();
				if(r.next())
					entrySetId=r.getInt(1);
						
				sql="Insert into ENTRY_SETS(ENTRY_SET_ID,MAKE_ID,MARKET_ID,SEGMENT_ID, " +
				 "MONTH,YEAR,STATUS) Values(?,?,?,?,?,?,'START')";  
				
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1, entrySetId);
				stmt.setInt(2, entrySet.getMakeId());
				stmt.setInt(3, entrySet.getMarketId());
				stmt.setInt(4, entrySet.getSegmentId());
				stmt.setInt(5, entrySet.getMonth());
				stmt.setInt(6, entrySet.getYear());
				stmt.executeUpdate();
				
				for(int i=0;i<nodeList.size();i++)
				{
					for(int j=0;j<scenarioList.size();j++)
					{
						stmt = conn.prepareStatement("Insert into NODE_FACTS(ENTRY_SET_ID,NODE_ID,SCENARIO_ID)  " +
								" Values(?,?,?)");  
						stmt.setInt(1, entrySetId);
						stmt.setInt(2, nodeList.get(i).getNodeId());
						stmt.setInt(3, scenarioList.get(j).getScenarioId());
						stmt.executeUpdate();
					}
				}
				
				response=true;
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 	
	 		return response;
		
	}
	
	public boolean createBatchEntrySet(ArrayList<EntrySetDTO> entrySetList,ArrayList<ScenarioDTO> scenarioList)
	{
		int entrySetId=0;
		boolean response=false;
		Connection conn = null;
		
		for(int e=0;e<entrySetList.size();e++)
		{
		
		EntrySetDTO entrySet=entrySetList.get(e);
		if(entrySet!=null && entrySet.getSelected()!=null && entrySet.getSelected().equalsIgnoreCase("on"))
			
		{
			conn = ConnectionManager.getConnection();
		   String sql = " Select SQN_ENTRYSETS.NEXTVAL from dual";
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				r = stmt.executeQuery();
				if(r.next())
					entrySetId=r.getInt(1);
						
				sql="Insert into ENTRY_SETS(ENTRY_SET_ID,MAKE_ID,MARKET_ID,SEGMENT_ID, " +
				 "MONTH,YEAR,STATUS) Values(?,?,?,?,?,?,'START')";  
				
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1, entrySetId);
				stmt.setInt(2, entrySet.getMakeId());
				stmt.setInt(3, entrySet.getMarketId());
				stmt.setInt(4, entrySet.getSegmentId());
				stmt.setInt(5, entrySet.getMonth());
				stmt.setInt(6, entrySet.getYear());
				stmt.executeUpdate();
				
				NodeHierarchyService nhService=new NodeHierarchyService();
				ArrayList<NodeHierarchyDTO> nodeList=nhService.getKpiNodeList(entrySet.getSegmentId());
				for(int i=0;i<nodeList.size();i++)
				{
					for(int j=0;j<scenarioList.size();j++)
					{
						stmt = conn.prepareStatement("Insert into NODE_FACTS(ENTRY_SET_ID,NODE_ID,SCENARIO_ID)  " +
								" Values(?,?,?)");  
						stmt.setInt(1, entrySetId);
						stmt.setInt(2, nodeList.get(i).getNodeId());
						stmt.setInt(3, scenarioList.get(j).getScenarioId());
						stmt.executeUpdate();
					}
				}
				
				response=true;
			} 
			catch (Exception e1){e1.printStackTrace();response=false;break;}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception ex)
				{ex.printStackTrace();}stmt = null;conn = null;}
		}
		}
			
	 	
	 		return response;
		
	}


}
